IF EXISTS ( SELECT 'X' FROM sys.views WHERE name = 'vMasterDDLActivity' AND schema_id = 1 )
BEGIN
     DROP VIEW dbo.vMasterDDLActivity
END
GO

CREATE VIEW dbo.vMasterDDLActivity
AS
/*
 *
 * vMasterDDLActivity
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 12/05/2011, 1.00.0000
 *
 */
SELECT i.SrvrInstncFullName,
       i.SrvrInstncId, 
       SrvrDatabaseName     = d.DatabaseName,
       DDLActivityId        = l.EventLogId,
       DDLActivityDt        = l.LogDt,
       l.HostName,
       l.AppName,
       l.LoginName,
       l.DatabaseUserName,
       EventType            = l.MiscVC1,
       l.DatabaseName,
       SchemaName           = l.MiscVC2,
       ObjectName           = l.MiscVC3,
       ObjectType           = l.MiscVC4,
       l.SPID,
       TSQLCommand          = l.TraceText 
FROM   dbo.MasterEventLog l INNER JOIN dbo.SrvrInstncDatabase d
          ON l.SrvrInstncDatabaseId = d.SrvrInstncDatabaseId
       INNER JOIN dbo.SrvrInstnc i
          ON d.SrvrInstncId = i.SrvrInstncId 
WHERE  l.LogByApp          = 'LogAllDDL'
AND    l.LogByAppSubSystem = 'InsDDLActivity'
GO
